package util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 操作数据库
 * 
 * @author Administrator
 * 
 */
public class JdbcUtil {
	// com.microsoft.sqlserver.jdbc.SQLServerDriver-----SQLServer
	// jdbc:sqlserver://ip:port;DatabaseName=databaseName
	// oracle.jdbc.driver.OracleDriver -----Oracle
	// jdbc:oracle:thin:@ip地址:1521:SID
	
	public static String drive = "oracle.jdbc.driver.OracleDriver";

	public static String name = "water";

	public static String password = "water";

	public static String databaseName = "orcl";

	public static String ip = "192.168.0.13";

	public static String port = "1521";

	static {
		try {
			Properties pp = new Properties();
			String url = JdbcUtil.class.getResource("/").getPath();
			if(url.contains("%20"))
				url = url.replace("%20", " ");
			pp.load(new FileInputStream(url+"/jdbc.properties"));
			drive = pp.getProperty("drive");
			name = pp.getProperty("name");
			password = pp.getProperty("password");
			databaseName = pp.getProperty("databaseName");
			ip = pp.getProperty("ip");
			port = pp.getProperty("port");
			Class.forName(drive);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}


	/**
	 * 获取连接
	 * 
	 * @return
	 */
	public static Connection getConn() {
		StringBuffer sb = new StringBuffer();
		// sb.append("jdbc:sqlserver://").append(ip).append(":").append(port)
		// .append(";DatabaseName=").append(databaseName);
		sb.append("jdbc:oracle:thin:@" + ip + ":" + port + ":" + databaseName);
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(sb.toString(), name, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 获取语句处理对象
	 * 
	 * @param conn
	 * @return
	 */
	public static Statement getStmt(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	/**
	 * 执行增加 删除 修改的SQL
	 * 
	 * @param sql
	 * @param stmt
	 * @return
	 */
	public static boolean executeUpdate_Delete_Insert(String sql, Statement stmt) {
		boolean b = false;
		try {
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				b = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return b;
	}

	/**
	 * 执行查询语句
	 * 
	 * @param sql
	 * @param stmt
	 * @return
	 */
	public static ResultSet executeSelect(String sql, Statement stmt) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 关闭
	 * 
	 * @param rs
	 * @param stmt
	 */
	public static void close(ResultSet rs, Statement stmt) {
		try {
			if (null != rs)// && !rs.isClosed()
				rs.close();
			if (null != stmt)// && !stmt.isClosed()
				stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 带？号参数的预处理对象获取
	 * 
	 * @param sql
	 * @param conn
	 * @param o
	 * @return
	 */
	public static PreparedStatement getPstmt(String sql, Connection conn,
			Object[] o) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			if (null != o && o.length > 0) {
				for (int i = 0; i < o.length; i++) {
					pstmt.setObject(i + 1, o[i]);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	/**
	 * 不带？号参数的预处理对象获取
	 * 
	 * @param sql
	 * @param conn
	 * @return
	 */
	public static PreparedStatement getPstmt(String sql, Connection conn) {
		return getPstmt(sql, conn, null);
	}

	/**
	 * 预处理对象执行增删改
	 * 
	 * @param pstmt
	 * @return
	 */
	public static boolean executeUpdate_Delete_Insert(PreparedStatement pstmt) {
		boolean b = false;
		try {
			int i = pstmt.executeUpdate();
			if (i > -1) {
				b = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return b;
	}

	/**
	 * 预处理对象执行查询语句
	 * 
	 * @param pstmt
	 * @return
	 */
	public static ResultSet executeSelect(PreparedStatement pstmt) {
		ResultSet rs = null;
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void closeConn(Connection conn) {
		try {
			if (null != conn && !conn.isClosed())
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 关闭
	 * 
	 * @param rs
	 * @param pstmt
	 */
	public static void closePstmt(ResultSet rs, PreparedStatement pstmt) {
		try {
			if (null != rs)// && !rs.isClosed()
				rs.close();
			if (null != pstmt)
				pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
